"""
MySQL数据库备份模块

提供MySQL数据库的备份功能，支持数据导出和结构备份。
"""

import os
import subprocess
import tempfile
import datetime
import pymysql
from typing import List, Dict, Any, Optional
from ..core import DatabaseBackup


class MySQLBackup(DatabaseBackup):
    """MySQL数据库备份类
    
    实现MySQL数据库的备份功能。
    """
    
    def __init__(self, host: str, port: int, username: str, password: str, 
                 database: str, backup_dir: str = "backups",
                 mysqldump_path: str = "mysqldump"):
        """初始化MySQL备份实例
        
        参数:
            host (str): MySQL主机地址
                示例值: "localhost"
            port (int): MySQL端口
                示例值: 3306
            username (str): MySQL用户名
                示例值: "root"
            password (str): MySQL密码
                示例值: "password123"
            database (str): 数据库名称
                示例值: "my_database"
            backup_dir (str): 备份文件存储目录
                示例值: "backups"
            mysqldump_path (str): mysqldump命令路径
                示例值: "mysqldump"
        
        返回:
            MySQLBackup: MySQL备份实例
        """
        super().__init__(host, port, username, password, database, backup_dir)
        self.mysqldump_path = mysqldump_path
        self.connection = None
    
    def connect(self) -> bool:
        """连接到MySQL数据库
        
        返回:
            bool: 连接是否成功
                示例值: True
        """
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.username,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            self.logger.info(f"成功连接到MySQL数据库 {self.database}")
            return True
        except Exception as e:
            self.logger.error(f"连接MySQL数据库失败: {e}")
            return False
    
    def disconnect(self) -> bool:
        """断开MySQL数据库连接
        
        返回:
            bool: 断开是否成功
                示例值: True
        """
        try:
            if self.connection:
                self.connection.close()
                self.connection = None
                self.logger.info("已断开MySQL数据库连接")
            return True
        except Exception as e:
            self.logger.error(f"断开MySQL数据库连接失败: {e}")
            return False
    
    def get_tables(self) -> List[str]:
        """获取MySQL数据库中的所有表名
        
        返回:
            List[str]: 表名列表
                示例值: ["users", "products", "orders"]
        """
        if not self.connection:
            self.logger.error("数据库未连接")
            return []
        
        try:
            with self.connection.cursor() as cursor:
                cursor.execute("SHOW TABLES")
                tables = [row[0] for row in cursor.fetchall()]
                self.logger.info(f"获取到 {len(tables)} 个表")
                return tables
        except Exception as e:
            self.logger.error(f"获取表列表失败: {e}")
            return []
    
    def backup_table(self, table_name: str, output_file: str, 
                    include_data: bool = True) -> bool:
        """备份单个MySQL数据表
        
        参数:
            table_name (str): 表名
                示例值: "users"
            output_file (str): 输出文件路径
                示例值: "backups/users_20231201.sql"
            include_data (bool): 是否包含数据
                示例值: True
        
        返回:
            bool: 备份是否成功
                示例值: True
        """
        try:
            # 构建mysqldump命令
            cmd = [
                self.mysqldump_path,
                f"-h{self.host}",
                f"-P{self.port}",
                f"-u{self.username}",
                f"-p{self.password}",
                self.database,
                table_name
            ]
            
            # 如果不包含数据，只备份结构
            if not include_data:
                cmd.append("--no-data")
            
            # 执行备份命令
            with open(output_file, 'w', encoding='utf-8') as f:
                result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, 
                                      text=True, timeout=300)
            
            if result.returncode == 0:
                self.logger.info(f"成功备份表 {table_name} 到 {output_file}")
                return True
            else:
                self.logger.error(f"备份表 {table_name} 失败: {result.stderr}")
                return False
                
        except subprocess.TimeoutExpired:
            self.logger.error(f"备份表 {table_name} 超时")
            return False
        except Exception as e:
            self.logger.error(f"备份表 {table_name} 时发生错误: {e}")
            return False
    
    def backup_database(self, output_file: str, include_data: bool = True,
                       tables: Optional[List[str]] = None) -> Dict[str, Any]:
        """备份整个MySQL数据库
        
        参数:
            output_file (str): 输出文件路径
                示例值: "backups/full_backup_20231201.sql"
            include_data (bool): 是否包含数据
                示例值: True
            tables (Optional[List[str]]): 指定要备份的表（None表示备份所有表）
                示例值: ["users", "products"]
        
        返回:
            Dict[str, Any]: 备份结果信息
                示例值: {
                    "success": True,
                    "output_file": "backups/full_backup_20231201.sql",
                    "tables_backed_up": 10,
                    "file_size": "15.2MB"
                }
        """
        try:
            # 首先尝试使用mysqldump
            try:
                # 构建mysqldump命令
                cmd = [
                    self.mysqldump_path,
                    f"-h{self.host}",
                    f"-P{self.port}",
                    f"-u{self.username}",
                    f"-p{self.password}",
                    "--single-transaction",
                    "--routines",
                    "--triggers",
                    "--events",
                    self.database
                ]
                
                # 如果不包含数据，只备份结构
                if not include_data:
                    cmd.append("--no-data")
                
                # 如果指定了表，只备份这些表
                if tables:
                    cmd.extend(tables)
                
                # 执行备份命令
                with open(output_file, 'w', encoding='utf-8') as f:
                    result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, 
                                          text=True, timeout=1800)  # 30分钟超时
                
                if result.returncode == 0:
                    file_size = os.path.getsize(output_file)
                    file_size_str = self._format_file_size(file_size)
                    
                    result_info = {
                        "success": True,
                        "output_file": output_file,
                        "file_size": file_size_str,
                        "tables_backed_up": len(tables) if tables else "all"
                    }
                    
                    self.logger.info(f"成功备份数据库 {self.database} 到 {output_file}")
                    return result_info
                else:
                    self.logger.warning(f"mysqldump备份失败: {result.stderr}")
                    # 继续尝试使用Python库备份
            except (FileNotFoundError, subprocess.TimeoutExpired) as e:
                self.logger.warning(f"mysqldump不可用或超时: {e}")
                # 继续尝试使用Python库备份
            
            # 如果mysqldump不可用，使用Python库进行备份
            return self._backup_with_python(output_file, include_data, tables)
                
        except Exception as e:
            self.logger.error(f"备份数据库时发生错误: {e}")
            return {
                "success": False,
                "error": str(e)
            }
    
    def _backup_with_python(self, output_file: str, include_data: bool = True,
                           tables: Optional[List[str]] = None) -> Dict[str, Any]:
        """使用Python库备份MySQL数据库
        
        参数:
            output_file (str): 输出文件路径
            include_data (bool): 是否包含数据
            tables (Optional[List[str]]): 指定要备份的表
        
        返回:
            Dict[str, Any]: 备份结果信息
        """
        try:
            # 连接到数据库
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.username,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            try:
                with connection.cursor() as cursor:
                    # 获取所有表
                    if not tables:
                        cursor.execute("SHOW TABLES")
                        tables = [row[0] for row in cursor.fetchall()]
                    
                    # 创建备份文件
                    with open(output_file, 'w', encoding='utf-8') as f:
                        # 写入数据库信息
                        f.write(f"-- MySQL Database Backup\n")
                        f.write(f"-- Database: {self.database}\n")
                        f.write(f"-- Host: {self.host}:{self.port}\n")
                        f.write(f"-- Backup Time: {self._get_current_time()}\n\n")
                        
                        # 备份每个表
                        successful_tables = 0
                        for table in tables:
                            try:
                                # 备份表结构
                                cursor.execute(f"SHOW CREATE TABLE `{table}`")
                                create_table_sql = cursor.fetchone()[1]
                                f.write(f"-- Table: {table}\n")
                                f.write(f"DROP TABLE IF EXISTS `{table}`;\n")
                                f.write(f"{create_table_sql};\n\n")
                                
                                # 备份表数据
                                if include_data:
                                    cursor.execute(f"SELECT * FROM `{table}`")
                                    rows = cursor.fetchall()
                                    if rows:
                                        f.write(f"-- Data for table: {table}\n")
                                        for row in rows:
                                            # 构建INSERT语句
                                            values = []
                                            for value in row:
                                                if value is None:
                                                    values.append("NULL")
                                                elif isinstance(value, str):
                                                    values.append(f"'{self._escape_string(value)}'")
                                                else:
                                                    values.append(str(value))
                                            
                                            insert_sql = f"INSERT INTO `{table}` VALUES ({', '.join(values)});\n"
                                            f.write(insert_sql)
                                        f.write("\n")
                                
                                successful_tables += 1
                                self.logger.info(f"成功备份表 {table}")
                                
                            except Exception as e:
                                self.logger.error(f"备份表 {table} 失败: {e}")
                                continue
                    
                    file_size = os.path.getsize(output_file)
                    file_size_str = self._format_file_size(file_size)
                    
                    result_info = {
                        "success": successful_tables == len(tables),
                        "output_file": output_file,
                        "file_size": file_size_str,
                        "tables_backed_up": successful_tables,
                        "total_tables": len(tables)
                    }
                    
                    if result_info["success"]:
                        self.logger.info(f"成功备份数据库 {self.database} 到 {output_file}")
                    else:
                        self.logger.warning(f"部分表备份失败: {successful_tables}/{len(tables)}")
                    
                    return result_info
                    
            finally:
                connection.close()
                
        except Exception as e:
            self.logger.error(f"使用Python库备份失败: {e}")
            return {
                "success": False,
                "error": str(e)
            }
    
    def _escape_string(self, s: str) -> str:
        """转义字符串中的特殊字符
        
        参数:
            s (str): 要转义的字符串
        
        返回:
            str: 转义后的字符串
        """
        return s.replace("'", "''").replace("\\", "\\\\")
    
    def _get_current_time(self) -> str:
        """获取当前时间字符串
        
        返回:
            str: 当前时间字符串
        """
        return datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    def test_connection(self) -> Dict[str, Any]:
        """测试数据库连接
        
        返回:
            Dict[str, Any]: 连接测试结果
                示例值: {
                    "success": True,
                    "database_version": "8.0.33",
                    "connection_info": {
                        "host": "localhost",
                        "port": 3306,
                        "database": "my_database"
                    }
                }
        """
        try:
            if not self.connect():
                return {
                    "success": False,
                    "error": "连接失败"
                }
            
            with self.connection.cursor() as cursor:
                cursor.execute("SELECT VERSION()")
                version = cursor.fetchone()[0]
                
                cursor.execute("SELECT DATABASE()")
                current_db = cursor.fetchone()[0]
            
            result = {
                "success": True,
                "database_version": version,
                "current_database": current_db,
                "connection_info": {
                    "host": self.host,
                    "port": self.port,
                    "database": self.database
                }
            }
            
            self.disconnect()
            return result
            
        except Exception as e:
            return {
                "success": False,
                "error": str(e)
            }
    
    def get_database_info(self) -> Dict[str, Any]:
        """获取数据库详细信息
        
        返回:
            Dict[str, Any]: 数据库信息
                示例值: {
                    "tables_count": 15,
                    "total_size": "256MB",
                    "character_set": "utf8mb4",
                    "collation": "utf8mb4_general_ci"
                }
        """
        if not self.connect():
            return {
                "tables_count": 0,
                "total_size": "0B",
                "error": "连接失败"
            }
        
        try:
            with self.connection.cursor() as cursor:
                # 获取表数量
                cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s", 
                             (self.database,))
                tables_count = cursor.fetchone()[0]
                
                # 获取数据库大小
                cursor.execute("""
                    SELECT SUM(data_length + index_length) 
                    FROM information_schema.tables 
                    WHERE table_schema = %s
                """, (self.database,))
                total_size_bytes = cursor.fetchone()[0] or 0
                
                # 获取字符集和排序规则
                cursor.execute("""
                    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
                    FROM information_schema.SCHEMATA 
                    WHERE SCHEMA_NAME = %s
                """, (self.database,))
                charset_info = cursor.fetchone()
                
            result = {
                "tables_count": tables_count,
                "total_size": self._format_file_size(total_size_bytes),
                "character_set": charset_info[0] if charset_info else "unknown",
                "collation": charset_info[1] if charset_info else "unknown"
            }
            
            self.disconnect()
            return result
            
        except Exception as e:
            self.logger.error(f"获取数据库信息失败: {e}")
            return {
                "tables_count": 0,
                "total_size": "0B",
                "error": str(e)
            }